﻿CREATE PROCEDURE [dbo].[Hogar_Integrante_PorObraSocialYNroAfiliado]
    @IntegranteId INT,
	@ObraSocialId INT,
	@NroAfiliado BIGINT
AS
BEGIN

	SELECT [IntegranteId],
			[Dni],
			[Nombre],
			[Apellido],
			CASE WHEN [Activo] = 1 THEN 1 ELSE 0 END AS [Activo],
			[TipoDocumentoId],
			[FechaNacimiento],
			[Domicilio],
			[Telefono],
			[EstadoCivilId],
			[NivelEstudioId],
			CASE WHEN [NivelEstudioCompleto] = 1 THEN 1 ELSE 0 END AS [NivelEstudioCompleto],
			[PropiedadViviendaId],
			[BeneficioPrevisionalId],
			[Monto],
			[ObraSocialId],
			[NroAfiliado],
			[ServicioEmergenciaId],
			[CuotaCooperadora],
			[FechaBaja],
			ISNULL([ObservacionBaja],'') AS [ObservacionBaja],
			[FechaInscripcion],
			ISNULL([Entrevista],'') AS [Entrevista]
	FROM [dbo].[Integrante] WITH (NOLOCK)
	WHERE [IntegranteId] <> @IntegranteId AND [Activo] = 1 AND [ObraSocialId] = @ObraSocialId AND [NroAfiliado] = @NroAfiliado
END
